using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Collections;

namespace DbfToAccess.Frm
{
    public partial class ExportForm : Form
    {

        private const string TabelMasterJabatanA1 = "REF_JABATAN_A1";
        public string PathDBF
        {
            get
            {
                return this.txtPathDBF.Text;
            }
        }

        public string PathAccess
        {
            get
            {
                return this.txtPathAccess.Text;
            }
        }

        public string Bulan
        {
            get
            {
                return ddBulan.SelectedItem.ToString();
            }
        }

        public string Tahun
        {
            get
            {
                return cmbYear.SelectedItem.ToString();
            }
        }

        public string MasaPajak
        {
            get
            {
                return ddMasaPajak.SelectedItem.ToString();
            }
        }

        public string TahunPajak
        {
            get
            {
                return this.cmbYearTax.SelectedItem.ToString();
            }
        }

        public string BranchCode
        {
            get
            {
                return this.txtCabang.Text;
            }
        }

        public string MainNPWP
        {
            get
            {
                return this.txtNPWP.Text;
            }
        }

        public string FormType
        {
            get
            {
                return ddFormPajak.SelectedItem.ToString();
            }
        }

        public ExportForm()
        {
            InitializeComponent();
            BindDDFormPajak();
            BindDDBulan();
            BindcmbYear();
            BindDDMasaPajak();
            BindcmbYearTax();
        }

        private void BindDDBulan()
        {
            ArrayList lstBulan = new ArrayList();
            lstBulan.Add("1");
            lstBulan.Add("2");
            lstBulan.Add("3");
            lstBulan.Add("4");
            lstBulan.Add("5");
            lstBulan.Add("6");
            lstBulan.Add("7");
            lstBulan.Add("8");
            lstBulan.Add("9");
            lstBulan.Add("10");
            lstBulan.Add("11");
            lstBulan.Add("12");

            ddBulan.DataSource = lstBulan;

        }

        private void BindcmbYear()
        {
            ArrayList lstYear = new ArrayList();
            int thisYear = DateTime.Now.Year;

            for (int i = DateTime.Now.AddYears(-1).Year; i <= thisYear; i++)
            {
                lstYear.Add(i);
            }
            
            cmbYear.DataSource = lstYear;
        }

        private void BindcmbYearTax()
        {
            ArrayList lstYear = new ArrayList();
            int thisYear = DateTime.Now.Year;

            for (int i = DateTime.Now.AddYears(-1).Year; i <= thisYear; i++)
            {
                lstYear.Add(i);
            }

            cmbYearTax.DataSource = lstYear;
            cmbYear.SelectedItem = DateTime.Now.Year;
        }

        private void BindDDFormPajak()
        {
            ArrayList list = new ArrayList();
            list.Add("1721-A1");
            list.Add("1721-T");
            list.Add("1721-II");
           
            ddFormPajak.DataSource = list;
        }

        private void BindDDMasaPajak()
        {
            ArrayList lstBulan = new ArrayList();
            lstBulan.Add("1");
            lstBulan.Add("2");
            lstBulan.Add("3");
            lstBulan.Add("4");
            lstBulan.Add("5");
            lstBulan.Add("6");
            lstBulan.Add("7");
            lstBulan.Add("8");
            lstBulan.Add("9");
            lstBulan.Add("10");
            lstBulan.Add("11");
            lstBulan.Add("12");

            ddMasaPajak.DataSource = lstBulan;

        }

        private void cekField()
        {
            TextBox textBox = this.ActiveControl as TextBox;
            if (textBox == null) return;

            if (txtNPWP.Text.Length < 0)
            {
                MessageBox.Show("NPWP Master harus diisi");
                if (textBox == txtNPWP) this.ActiveControl = txtNPWP;
                
            }
            if (txtCabang.Text.Length < 0)
            {
                MessageBox.Show("Cabang harus diisi");
                if (textBox == txtCabang) this.ActiveControl = txtCabang;
            }
           
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            progressBar1.Visible = true;
            timer1.Enabled = true;
            timer1.Interval = 10;
            timer1.Start();
            bool finish = false;
            DataTable dt = new DataTable();
            dt = DataTableDBF();

            if (chkTable.Checked == true)
            {
                finish = ExportData(dt, TabelMasterJabatanA1);
            }
            else
            {
                if (FormType == "1721-T")
                {
                    finish = ExportData(TaxEmployeeToCollection(dt),FormType);
                }
                else if (FormType == "1721-II")
                {
                    finish = ExportData(TaxEmployeeToCollection(dt),FormType);
                }
                else if (FormType == "1721-A1")
                {
                    finish = ExportData(ListWithBranch(Data1721A1ToCollection(dt),BranchCode),MappingDsFormInduk(Data1721A1ToCollection(dt),BranchCode), FormType);
                }
            }

            if (finish)
            {
                MessageBox.Show("Export Data Berhasil");
            }
            else
            {
                MessageBox.Show("Export Data Gagal");
            }
        }

        protected void timer1_Tick (object sender, System.EventArgs e)
        {
            progressBar1.Value += 10;

            if (progressBar1.Value >= 200)
            {
                timer1.Enabled = false;
                progressBar1.Value = 0;
            }

        }

        private DataTable DataTableDBF()
        {
            string filePath = Path.GetPathRoot(PathDBF);
            string fullPath = Path.GetFullPath(PathDBF);
            DataTable dt = new DataTable();

            OdbcConnection CC = new OdbcConnection("Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" + filePath + ";Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;");
            try
            {
                CC.Open();

                OdbcCommand cmd = new OdbcCommand("Select * From " + fullPath, CC);
                OdbcDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    dt = new DataTable();
                    dt.Load(dr);
                }
                CC.Close();
            }
            catch (Exception e)
            {
                e.ToString();
                CC.Close();
            }

            return dt;
        }

        private List<TaxEmployee> TaxEmployeeToCollection(DataTable dt)
        {
            TaxEmployee oMap;
            List<TaxEmployee> olist = new List<TaxEmployee>();

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    oMap = new TaxEmployee();

                    oMap.Cabang = dr[0].ToString();
                    oMap.NIK = dr[1].ToString();
                    oMap.NPWP = dr[2].ToString();
                    oMap.Nama = dr[3].ToString();
                    oMap.StatusPernikahan = dr[4].ToString();
                    oMap.Tanggungan = dr[5].ToString();
                    oMap.Bruto = Convert.ToDouble(dr[6]);
                    oMap.PPH21 = Convert.ToDouble(dr[7]);
                    oMap.Masuk = dr[8].ToString();
                    oMap.Keluar = dr[9].ToString();
                    oMap.TglNPWP = dr[10].ToString();

                    olist.Add(oMap);
                }
            }

            return olist;
        }

        private List<Data1721A1> Data1721A1ToCollection(DataTable dt)
        {
            List<Data1721A1> olist = new List<Data1721A1>();
            string kodeForm = "D113248";
            int i = Tools.GetLastNumber("NO_URUT", "DAT_1721A1",PathAccess);
            string masaPajak1 = string.Empty;
            string masaPajak2 = string.Empty;
           
            Data1721A1 oMap;
            
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    string msA = dr[9].ToString();
                    string msZ = dr[10].ToString();

                    string npwpPegawai = string.Empty;
                    int length = 0;
                    length = dr[8].ToString().Length;

                    if (dr[8].ToString() == "0")
                    {
                        npwpPegawai = "000000000000000";
                    }
                    else if (dr[8].ToString() == string.Empty)
                    {
                        npwpPegawai = "000000000000000";
                    }
                    else
                    {
                        npwpPegawai = Tools.JoinWithoutDot(dr[8].ToString());
                    }

                    if (msA.Length == 1)
                    {
                        masaPajak1 = 0 + msA + TahunPajak;
                    }
                    else
                    {
                        masaPajak1 = msA + TahunPajak;
                    }

                    if (msZ.Length == 1)
                    {
                        masaPajak2 = 0 + msZ + TahunPajak;
                    }
                    else
                    {
                        masaPajak2 = msZ + TahunPajak;
                    }
                    oMap = new Data1721A1();

                    oMap.BranchCode = dr[3].ToString();
                    oMap.KdForm = kodeForm;
                    //oMap.NoUrut = i;
                    oMap.NoUrut = Convert.ToInt32(dr[0]);
                    oMap.NpwpPerusahaan = MainNPWP;
                    oMap.TahunPajak = Convert.ToInt32(TahunPajak);
                    oMap.KdPembentulanKe = 0;
                    oMap.NpwpPegawai = npwpPegawai;
                    oMap.NamaPegawai = (dr[5] is DBNull) ? string.Empty : Tools.RemovePetik(dr[5].ToString());
                    oMap.AlamatPegawai = dr[16].ToString();
                    oMap.JabatanPegawai = dr[14].ToString();
                    oMap.JenisKelamin = Tools.JenisKelamin(dr[6].ToString());
                    oMap.FlagAsing = 0;
                    oMap.StatusKawin = Tools.KodeStatus(dr[7].ToString());
                    oMap.StatusKeluarga = Tools.KodeStatus(dr[7].ToString());
                    oMap.JumlahTanggungan = Tools.JmlTanggungan(dr[7].ToString());
                    oMap.Masa1 = masaPajak1.ToString();
                    oMap.Masa2 = masaPajak2.ToString();
                    oMap.Angka1 = (dr[50] is DBNull) ? 0 : Convert.ToDouble(dr[50]);
                    oMap.Angka2 = 0;
                    oMap.Angka3 = (dr[51] is DBNull) ? 0 : Convert.ToDouble(dr[51]);
                    oMap.Angka4 = 0;
                    oMap.Angka5 = (dr[52] is DBNull) ? 0 : Convert.ToDouble(dr[52]);
                    oMap.Angka6 = 0;
                    oMap.Angka7 = (dr[53] is DBNull) ? 0 : Convert.ToDouble(dr[53]);
                    oMap.Angka8 = (dr[54] is DBNull) ? 0 : Convert.ToDouble(dr[54]);
                    oMap.Angka9 = (dr[55] is DBNull) ? 0 : Convert.ToDouble(dr[55]);
                    oMap.Angka10 = Convert.ToDouble(dr[56]);
                    oMap.Angka11 = (dr[57] is DBNull) ? 0 : Convert.ToDouble(dr[57]);
                    oMap.Angka12 = (dr[58] is DBNull) ? 0 : Convert.ToDouble(dr[58]);
                    oMap.Angka13 = (dr[59] is DBNull) ? 0 : Convert.ToDouble(dr[59]);
                    oMap.Angka14 = (dr[60] is DBNull) ? 0 : Convert.ToDouble(dr[60]);
                    oMap.Angka15 = (dr[61] is DBNull) ? 0 : Convert.ToDouble(dr[61]);
                    oMap.Angka16 = (dr[62] is DBNull) ? 0 : Convert.ToDouble(dr[62]);
                    oMap.Angka17 = (dr[63] is DBNull) ? 0 : Convert.ToDouble(dr[63]);
                    oMap.Angka18 = (dr[64] is DBNull) ? 0 : Convert.ToDouble(dr[64]);
                    oMap.Angka19 = (dr[65] is DBNull) ? 0 : Convert.ToDouble(dr[65]);
                    oMap.Angka20 = (dr[66] is DBNull) ? 0 : Convert.ToDouble(dr[66]);
                    oMap.Angka21 = (dr[67] is DBNull) ? 0 : Convert.ToDouble(dr[67]);
                    oMap.Angka22 = (dr[69] is DBNull) ? 0 : Convert.ToDouble(dr[69]);
                    oMap.Angka22A = 0;
                    oMap.Angka22B = oMap.Angka22;
                    oMap.Angka23 = (dr[70] is DBNull) ? 0 : Convert.ToDouble(dr[70]);
                    oMap.Angka24 = (dr[71] is DBNull) ? 0 : Convert.ToDouble(dr[71]);
                    oMap.FlagStatus = 0;
                    oMap.Flag23 = 0;
                    oMap.Flag24 = 0;
                    oMap.MasaTahungPotong = string.Empty;
                    oMap.MasaTahungHitung = string.Empty;
                    oMap.UmrBulan = 0;
                    oMap.UmrTahun = 0;
                    oMap.FlagTunjanganPPH = 1;
                    oMap.FlagPTKP = 2;
                    oMap.FlagPegawai = 1;
                    olist.Add(oMap);

                    i = i + 1;
                }
                catch (Exception e)
                {
                    e.ToString();
                    olist = new List<Data1721A1>();
                    break;
                }
            }
        
            return olist;
        }

        private List<DS_Form_Induk> MappingDsFormInduk(List<Data1721A1> dt,string kdCabang)
        {
            DS_Form_Induk oMap = null;
            List<DS_Form_Induk> oList = new List<DS_Form_Induk>();

            int i = 0;
            string datForm = string.Empty;
            int jmlPerson = 0;
            double a9 = 0;
            double gg = 0;
            double a21 = 0;

            double valC = 0;
            double valD = 0;

            string a = string.Empty, b = string.Empty, c = string.Empty, d = string.Empty,
                e = string.Empty, f = string.Empty, g = string.Empty, h = string.Empty;

            foreach (Data1721A1 dr in dt)
            {
                if (dr.BranchCode == kdCabang)
                {
                    if (dr.Angka21 > 0)
                    {
                        valC = valC + dr.Angka9;
                        valD = valD + dr.Angka21;
                    }
                    else if (dr.Angka21 == 0)
                    {
                        i = i + 1;
                        a9 = a9 + dr.Angka9;
                        a21 = a21 + dr.Angka21;
                    }
                }
            }

            jmlPerson = i;

            a = TahunPajak.Trim();
            b = "0";
            c = valC.ToString();

            d = valD.ToString();
            e = a9.ToString();
            f = jmlPerson.ToString();

            gg = Convert.ToDouble(e) + Convert.ToDouble(c);
            g = gg.ToString();
            h = d;

           
            oMap = new DS_Form_Induk();

            datForm = a + ";" + b + ";" + c + ";" + d + ";" + e + ";" + f + ";" + g + ";" + h;

            oMap.NPWP = MainNPWP.Trim();
            oMap.Kd_Form = "D113247";
            oMap.Jenis_Dokumen = "02";
            oMap.MasaPajak = Convert.ToInt32(this.MasaPajak.Trim());
            oMap.Th_Pajak = Convert.ToInt32(this.TahunPajak.Trim());
            oMap.KD_PEMBETULAN_KE = 0;
            oMap.NOBUKTI = "-";
            oMap.KD_JENIS_PAJAK = "111";
            oMap.DAT_FORM = datForm;
            oMap.TGL_REKAM = DateTime.Now;
            oMap.TGL_UBAH = DateTime.Now;
            oMap.NIP = "";

            oList.Add(oMap);

            string datForm2 = string.Empty;
            string nmPerush = string.Empty;
            string almtPerush = string.Empty;

            DS_Form_Induk oMap2 = new DS_Form_Induk();

            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = connectionString;
            con.Open();
            string strSelect = string.Empty;

            strSelect = "select NAMA_WP_PROFILE,ALAMAT from DAT_PROFILE where NPWP = '" + MainNPWP + "'";

            OleDbCommand cmdExist = con.CreateCommand();
            cmdExist.CommandText = strSelect;
            cmdExist.CommandType = CommandType.Text;

            OleDbDataReader rdExist = cmdExist.ExecuteReader();

            while (rdExist.Read())
            {
                nmPerush = rdExist[0].ToString();
                almtPerush = rdExist[1].ToString();
            }

            con.Close();
            con.Dispose();

            datForm2 = MasaPajak + ";" + TahunPajak + ";0;0;" + TahunPajak + ";" + MainNPWP + ";" + nmPerush + ";" + almtPerush + ";;;01/01/2010;" + f.ToString() + ";" + g.ToString() + ";" + h.ToString() + ";0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;" + jmlPerson + ";" + a9 + ";0;0;0;0;;0;0;0;0;0;0;0;0;;;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;;1";
            
            oMap2.NPWP = MainNPWP.Trim();
            oMap2.Kd_Form = "F113201".Trim();
            oMap2.Jenis_Dokumen = "01";
            oMap2.MasaPajak = Convert.ToInt32(this.MasaPajak.Trim());
            oMap2.Th_Pajak = Convert.ToInt32(this.TahunPajak.Trim());
            oMap2.KD_PEMBETULAN_KE = 0;
            oMap2.NOBUKTI = "-";
            oMap2.KD_JENIS_PAJAK = "111";
            oMap2.DAT_FORM = datForm2;
            oMap2.TGL_REKAM = DateTime.Now;
            oMap2.TGL_UBAH = DateTime.Now;
            oMap2.NIP = "";

            oList.Add(oMap2);
           
            return oList;
 
        }

        private List<Data1721A1> ListWithBranch(List<Data1721A1> dt, string branchCode)
        {
            List<Data1721A1> dtBranch = new List<Data1721A1>();

            foreach (Data1721A1 dr in dt)
            {
                if (dr.BranchCode.Trim().Equals(branchCode.Trim(),StringComparison.OrdinalIgnoreCase))
                {
                    dtBranch.Add(dr);
                }
            }

            return dtBranch;
        }

        private List<REF_JABATAN_A1Map> GetLstRefJabatan(DataTable dt)
        {
            List<REF_JABATAN_A1Map> oList = new List<REF_JABATAN_A1Map>();
            Dictionary<string, int> dictionary = new Dictionary<string, int>();

            foreach (DataRow dr in dt.Rows)
            {
                if (dr[0].ToString().Trim() != string.Empty)
                {
                    if (!dictionary.ContainsKey(dr[0].ToString()))
                    {
                        dictionary.Add(dr[0].ToString(), 0);
                        REF_JABATAN_A1Map oMap = new REF_JABATAN_A1Map();
                        oMap.ID_JAB = dr[0].ToString();
                        oMap.NM_JAB = dr[1].ToString();
                        oList.Add(oMap);
                    }
                }
            }

            return oList;
        }

        private bool ExportData(DataTable dt,string formtType)
        {
            bool ok = false;
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            string strInsertIN = string.Empty;
            string strInsertOut = string.Empty;
            string deleteIn = string.Empty;
            string deleteOut = string.Empty;

            List<REF_JABATAN_A1Map> oList = new List<REF_JABATAN_A1Map>();
            oList = GetLstRefJabatan(dt);

            if (formtType == "REF_JABATAN_A1")
            {
                 if (Tools.IsHasData("", "REF_JABATAN_A1", PathAccess))
                {
                    /* hapus data master jabatan sebelumnya */
                    deleteIn = "delete from REF_JABATAN_A1";
                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = connectionString;
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = deleteIn;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();
                }
                else
                {
                    if (Tools.IsHasData("", "REF_JABATAN_A1", PathAccess))
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            deleteIn = "delete from REF_JABATAN_A1 where ID_JAB = '" + Convert.ToDouble(dr[0]) + "'";
                            OleDbConnection con = new OleDbConnection();
                            con.ConnectionString = connectionString;
                            con.Open();
                            OleDbCommand cmd = con.CreateCommand();
                            cmd.CommandText = deleteIn;
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            con.Close();
                        }
                    }
                }

                deleteIn = "delete from REF_JABATAN_A1";
                OleDbConnection con2 = new OleDbConnection();
                con2.ConnectionString = connectionString;
                con2.Open();
                OleDbCommand cmd2 = con2.CreateCommand();
                cmd2.CommandText = deleteIn;
                cmd2.CommandType = CommandType.Text;
                cmd2.ExecuteNonQuery();
                cmd2.Dispose();
                con2.Close();

                foreach (REF_JABATAN_A1Map oMap in oList)
                {
                    strInsertIN = "insert into REF_JABATAN_A1(ID_JAB,NM_JAB)" +
                                "values(" + "'" + oMap.ID_JAB.ToString() + "','" + oMap.NM_JAB.ToString().Replace("'", " ") + "'" + ")";

                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = connectionString;
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = strInsertIN.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();
                }

                ok = true;
            }

            return ok;
        }

        private List<Data1721A1> GetLstData1721A1(List<Data1721A1> oListParam)
        {
            Dictionary<string, int> dict = new Dictionary<string, int>();
            List<Data1721A1> oList = new List<Data1721A1>();
            int i = 1;

            foreach (Data1721A1 oMap in oListParam)
            {
                if (!oMap.NpwpPegawai.Trim().Equals(string.Empty))
                {
                    if (!dict.ContainsKey(oMap.NpwpPegawai.Trim() + oMap.NoUrut))
                    {
                        dict.Add(oMap.NpwpPegawai.Trim() + oMap.NoUrut, i);
                        oList.Add(oMap);
                        i += 1;
                    }
                }
            }

            return oList;
        }

        private List<Data1721A1> GetLstDataUnique1721A1(List<Data1721A1> oListParam)
        {
            Dictionary<string, int> dict = new Dictionary<string, int>();
            List<Data1721A1> oList = new List<Data1721A1>();
            int i = 1;

            foreach (Data1721A1 oMap in oListParam)
            {
                if (!oMap.NpwpPegawai.Trim().Equals(string.Empty))
                {
                    if (!dict.ContainsKey(oMap.NpwpPegawai.Trim()))
                    {
                        dict.Add(oMap.NpwpPegawai.Trim(), i);
                        oList.Add(oMap);
                        i += 1;
                    }
                }
            }

            return oList;
        }

        private bool ExportData(List<Data1721A1> oListParam, List<DS_Form_Induk> lstFormInduk, string formtType)
        {
            string strInsertIN = string.Empty;
            string strInsertOut = string.Empty;
            string deleteIn = string.Empty;
            string deleteOut = string.Empty;
            string mainNPWP = MainNPWP;
            string tblJabatan = "DAT_1721A1";
            bool ok = false;

            List<Data1721A1> olist = new List<Data1721A1>();
            olist = GetLstData1721A1(oListParam);
            olist.Sort();
           
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = connectionString;

            if (formtType == "1721-A1")
            {
                if (Tools.IsHasData("", tblJabatan, PathAccess))
                {
                    /* delete all */
                    deleteIn = "delete from DAT_1721A1"; 
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = deleteIn;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();
                  }

                  foreach (Data1721A1 d in olist)
                  {
                      try
                      {
                          strInsertIN = "insert into DAT_1721A1(KD_FORM,NO_URUT,NPWP_PERSH,TH_PAJAK,KD_PEMBETULAN_KE,NPWP_PEG,NAMA_PEG,ALM_PEG," +
                                        " JAB_PEG,JNS_KEL,FLG_ASING,STS_KWN,STS_KEL,JML_TGN,MASA_1,MASA_2,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12," +
                                        " A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A22a,A22b,A23,A24,FLG_STS,FLG_23,FLG_24,MS_THN_POT,MS_THN_HITUNG,UMRBLN,UMRTHN,FLG_TNJ_PPH,FLG_PTKP,FLG_PEG)" +
                                      "values(" + "'" + d.KdForm + "'," + "'" + d.NoUrut + "'," + "'" + d.NpwpPerusahaan + "'," + "'" + d.TahunPajak + "'," + "'" + d.KdPembentulanKe +
                                      "'," + "'" + d.NpwpPegawai + "'," + "'" + Tools.RemovePetik(d.NamaPegawai) + "'," + "'" + Tools.RemovePetik(d.AlamatPegawai) +
                                      "'," + "'" + d.JabatanPegawai + "'," + "'" + d.JenisKelamin + "'," + "'" + d.FlagAsing +
                                      "'," + "'" + d.StatusKawin + "'," + "'" + d.StatusKeluarga + "'," + "'" + d.JumlahTanggungan +
                                      "'," + "'" + d.Masa1 + "'," + "'" + d.Masa2 + "'," + "'" + d.Angka1 +
                                      "'," + "'" + d.Angka2 + "'," + "'" + d.Angka3 + "'," + "'" + d.Angka4 +
                                      "'," + "'" + d.Angka5 + "'," + "'" + d.Angka6 + "'," + "'" + d.Angka7 +
                                      "'," + "'" + d.Angka8 + "'," + "'" + d.Angka9 + "'," + "'" + d.Angka10 +
                                      "'," + "'" + d.Angka11 + "'," + "'" + d.Angka12 + "'," + "'" + d.Angka13 +
                                      "'," + "'" + d.Angka14 + "'," + "'" + d.Angka15 + "'," + "'" + d.Angka16 +
                                      "'," + "'" + d.Angka17 + "'," + "'" + d.Angka18 + "'," + "'" + d.Angka19 +
                                      "'," + "'" + d.Angka20 + "'," + "'" + d.Angka21 + "'," + "'" + d.Angka22 +
                                      "'," + "'" + d.Angka22A + "'," + "'" + d.Angka22B + "'," + "'" + d.Angka23 +
                                      "'," + "'" + d.Angka24 + "'," + "'" + d.FlagStatus + "'," + "'" + d.Flag23 + "'" + ",'" + d.Flag24 +
                                      "'," + "'" + d.MasaTahungPotong + "'," + "'" + d.MasaTahungHitung + "'," + "'" + d.UmrBulan +
                                      "'," + "'" + d.UmrTahun + "'," + "'" + d.FlagTunjanganPPH + "'," + "'" + d.FlagPTKP +
                                      "'," + "'" + d.FlagPegawai + "'" +
                                      ")";

                          con.Open();
                          OleDbCommand cmd = con.CreateCommand();
                          cmd.CommandText = strInsertIN.ToString();
                          cmd.CommandType = CommandType.Text;
                          cmd.ExecuteNonQuery();
                          cmd.Dispose();
                          con.Close();
                      }
                      catch (Exception e)
                      {
                          e.ToString();
                          con.Close();
                      }
                  }

                deleteIn = string.Empty;
                strInsertIN = string.Empty;

                if (Tools.IsHasData("", "DS_FORM_INDUK", PathAccess))
                {
                    int msPajak = 0;
                    int thPajak = 0;
                    string kdForm = string.Empty;

                    foreach (DS_Form_Induk s in lstFormInduk)
                    {
                        msPajak = s.MasaPajak;
                        thPajak = s.Th_Pajak;
                        kdForm = s.Kd_Form.Trim();

                        deleteIn = "delete from DS_FORM_INDUK where KD_FORM = '" + kdForm + "'" +
                                   " and MS_PAJAK = " + msPajak + "" + " and TH_PAJAK = " + thPajak + "" + "";
                        
                        con.Open();
                        OleDbCommand cmd = con.CreateCommand();
                        cmd.CommandText = deleteIn;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        con.Close();
                    }
                   
                }

                foreach (DS_Form_Induk ss in lstFormInduk)
                {
                    strInsertIN = "insert into DS_FORM_INDUK(NPWP,KD_FORM,JENIS_DOKUMEN,MS_PAJAK,TH_PAJAK,KD_PEMBETULAN_KE,NOBUKTI,KD_JENIS_PAJAK,DAT_FORM,TGL_REKAM,TGL_UBAH,NIP)" +
                             "values('" + ss.NPWP + "','" + ss.Kd_Form + "','" + ss.Jenis_Dokumen + "','" +
                             ss.MasaPajak + "','" + ss.Th_Pajak + "','" + ss.KD_PEMBETULAN_KE + "','" + ss.NOBUKTI + "','" + ss.KD_JENIS_PAJAK + "','" +
                             ss.DAT_FORM + "','" + ss.TGL_REKAM + "','" + ss.TGL_UBAH + "','" + ss.NIP + "'" + ")";

                    OleDbConnection conInsert = new OleDbConnection();
                    conInsert.ConnectionString = connectionString;
                    conInsert.Open();
                    OleDbCommand cmdInsert = conInsert.CreateCommand();
                    cmdInsert.CommandText = strInsertIN.ToString();
                    cmdInsert.CommandType = CommandType.Text;
                    cmdInsert.ExecuteNonQuery();
                    cmdInsert.Dispose();
                    conInsert.Close();
                }

                deleteIn = string.Empty;
                strInsertIN = string.Empty;

                if (Tools.IsHasData("", "DAT_PEG_1721A1", PathAccess))
                {
                    /* delete all */
                        deleteIn = "delete from DAT_PEG_1721A1";
                        con.Open();
                        OleDbCommand cmd = con.CreateCommand();
                        cmd.CommandText = deleteIn;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        con.Close();
                }
                
                // unique
                List<Data1721A1> lstData1721A1Unique = new List<Data1721A1>();
                lstData1721A1Unique = GetLstDataUnique1721A1(olist);

                foreach (Data1721A1 d in lstData1721A1Unique)
                {
                    strInsertIN = "insert into DAT_PEG_1721A1(NPWP_PEG,NAMA_PEG,ALAMAT_PEG,JAB_PEG,JNS_KLM_PEG,STS_PEG,JML_TG_PEG,FLAG_ASING,STS_KWN,FLAG_PEG)" +
                                "values('" + d.NpwpPegawai + "','" + Tools.RemovePetik(d.NamaPegawai) + "','" + Tools.RemovePetik(d.AlamatPegawai) + "','"  + Tools.RemovePetik(d.JabatanPegawai) + "','" + 
                                        d.JenisKelamin + "','" + d.StatusKawin + "','" + d.JumlahTanggungan + "','" + d.FlagAsing + "','" + d.StatusKawin + "','" + d.FlagStatus + "')";

                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = strInsertIN.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();
                }

                ok = true;
            }

            return ok;
        }

        private bool ExportData(List<TaxEmployee> olist, string formtType)
        {
            string strInsertIN = string.Empty;
            string strInsertOut = string.Empty;
            string deleteIn = string.Empty;
            string deleteOut = string.Empty;
            string mainNPWP = MainNPWP;
            bool ok = false;
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";

            if (formtType == "1721-T")
            {
                int month = Convert.ToInt32(Bulan);
                int year = Convert.ToInt32(Tahun);
                string cabangPilih = BranchCode;

                List<TaxEmployee> olistCabang = new List<TaxEmployee>();
                foreach (TaxEmployee oCabang in olist)
                {
                    if ((Convert.ToDateTime(oCabang.Masuk).Month >= month
                          && Convert.ToDateTime(oCabang.Masuk).Year >= year)
                          )
                    {
                        olistCabang.Add(oCabang);
                    }
                }
                
                /* delete data yang sudah ada utk menghindarai insert double */
                foreach (TaxEmployee rpt in olistCabang)
                {
                    deleteIn = "delete from DAT_1721T where NPWP_PEG = '" + Tools.RemoveDelimeter(rpt.NPWP) + "'";
                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = connectionString;
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = deleteIn;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();

                }

                foreach (TaxEmployee rpt in olistCabang)
                {
                    strInsertIN = "insert into DAT_1721T(NPWP,MS_PAJAK,TH_PAJAK,TH_TAKWIM,NPWP_PEG,NAMA_PEG,KD_STATUS,JML_TTG)" +
                                "values(" + "'" + mainNPWP + "'," + "'" + MasaPajak + "'," + "'" + TahunPajak + "'," + "'" + TahunPajak + "'," + "'" + Tools.RemoveDelimeter(rpt.NPWP) +
                                "'," + "'" + Tools.RemovePetik(rpt.Nama) + "'," + "'" + Tools.KodeStatus(rpt.StatusPernikahan) + "'," + "'" + Tools.JmlTanggungan(rpt.Tanggungan) + "'"
                                + ")";

                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = connectionString;
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = strInsertIN.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();
                }
                ok = true;
                
            }
            else if (formtType == "1721-II")
            {
               
                int month = Convert.ToInt32(Bulan);
                int year = Convert.ToInt32(Tahun);
                string cabang = BranchCode;
              
                List<TaxEmployee> olistKeluar = new List<TaxEmployee>();
                List<TaxEmployee> olistMasuk = new List<TaxEmployee>();
                List<TaxEmployee> olistBaru = new List<TaxEmployee>();

                foreach (TaxEmployee oKeluar in olist)
                {
                    if (cabang != string.Empty)
                    {
                        if (oKeluar.Cabang == cabang)
                        {
                            olistKeluar.Add(oKeluar);
                        }
                    }
                }

                foreach (TaxEmployee oMasuk in olist)
                {
                    if (cabang != string.Empty)
                    {
                        if (oMasuk.Cabang == cabang)
                        {
                            if ((Convert.ToDateTime(oMasuk.Masuk).Month >= month
                            && Convert.ToDateTime(oMasuk.Masuk).Year >= year)
                            )
                            {
                                olistMasuk.Add(oMasuk);
                            }
                        }
                    }
                }

                foreach (TaxEmployee oBaru in olist)
                {
                    if (cabang != string.Empty)
                    {
                        if (oBaru.Cabang == cabang)
                        {
                            if ((Convert.ToDateTime(oBaru.TglNPWP).Month == month
                           && Convert.ToDateTime(oBaru.TglNPWP).Year == year)
                           )
                            {
                                olistBaru.Add(oBaru);
                            }
                        }
                    }
                }
               
                /* hapus data yang sudah ada */

                foreach (TaxEmployee rpt in olistMasuk)
                {
                    deleteIn = "delete from DAT_PEG_IN where NPWP_PEG = '" + Tools.RemoveDelimeter(rpt.NPWP) + "'";
                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = connectionString;
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = deleteIn;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();
                }

                /* insert data into ms access */

                foreach (TaxEmployee rpt in olistMasuk)
                {
                    strInsertIN = "insert into DAT_PEG_IN(NPWP,MS_PAJAK,TH_PAJAK,TH_TAKWIM,NPWP_PEG,NAMA_PEG,KD_STATUS,JML_TTG)" +
                                "values(" + "'" + mainNPWP + "'," + "'" + MasaPajak + "'," + "'" + TahunPajak + "'," + "'" + TahunPajak + "'," + "'" + Tools.RemoveDelimeter(rpt.NPWP) +
                                "'," + "'" + Tools.RemovePetik(rpt.Nama) + "'," + "'" + Tools.KodeStatus(rpt.StatusPernikahan) + "'," + "'" + Tools.JmlTanggungan(rpt.Tanggungan) + "'"
                                + ")";

                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = connectionString;
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = strInsertIN.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();

                }

                /* hapus data yang sudah ada */

                foreach (TaxEmployee rpt in olistKeluar)
                {
                    deleteOut = "delete from DAT_PEG_OUT where NPWP_PEG = '" + Tools.RemoveDelimeter(rpt.NPWP) + "'";
                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = connectionString;
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = deleteOut;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();

                }

                foreach (TaxEmployee rpt in olistKeluar)
                {
                    strInsertOut = "insert into DAT_PEG_OUT(NPWP,MS_PAJAK,TH_PAJAK,TH_TAKWIM,NPWP_PEG,NAMA_PEG,JML_BRUTO,JML_PPH)" +
                                "values(" + "'" + mainNPWP + "'," + "'" + MasaPajak + "'," + "'" + TahunPajak + "'," + "'" + TahunPajak + "'," + "'" + Tools.RemoveDelimeter(rpt.NPWP) +
                                "'," + "'" + Tools.RemovePetik(rpt.Nama) + "'," + "'" + rpt.Bruto + "'," + "'" + rpt.PPH21 + "'"
                                + ")";

                    OleDbConnection con = new OleDbConnection();
                    con.ConnectionString = connectionString;
                    con.Open();
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandText = strInsertOut.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    con.Close();

                }

                ok = true;

            }
            
            return ok;
        }

        private void btnSource_Click(object sender, EventArgs e)
        {
            openFileDialog.Title = "Open DBF File";
            openFileDialog.Filter = "DBF Files|*.dbf";
            openFileDialog.CheckFileExists = true;
            openFileDialog.CheckPathExists = true;
            openFileDialog.InitialDirectory = @"C:\";
            openFileDialog.ShowDialog();
            txtPathDBF.Text = openFileDialog.FileName.ToString();
        }

        private void btnDestination_Click(object sender, EventArgs e)
        {
            openFileDialog1.Title = "Open MDB File";
            openFileDialog1.Filter = "MDB Files|*.mdb";
            openFileDialog1.CheckFileExists = true;
            openFileDialog1.CheckPathExists = true;
            openFileDialog1.InitialDirectory = @"C:\Program Files\DJP\eSPT PPh Masa Pasal 21-26\Database";
            openFileDialog1.ShowDialog();
            txtPathAccess.Text = openFileDialog1.FileName.ToString();
            txtCabang.Text = Path.GetFileNameWithoutExtension(txtPathAccess.Text);
            txtNPWP.Text = NPWP(DtDAT_PROFILE());
            btnExport.Enabled = true;
        }

        private void ddFormPajak_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.ddFormPajak.SelectedIndex == 0)
            {
                this.label3.Visible = false;
                this.label4.Visible = false;
                this.ddBulan.Visible = false;
                this.cmbYear.Visible = false;
            }
            else if (this.ddFormPajak.SelectedIndex == 1)
            {
                this.label3.Visible = true;
                this.label4.Visible = true;
                this.ddBulan.Visible = true;
                this.cmbYear.Visible = true;
            }
            else if (this.ddFormPajak.SelectedIndex == 2)
            {
                this.label3.Visible = true;
                this.label4.Visible = true;
                this.ddBulan.Visible = true;
                this.cmbYear.Visible = true;
            }
        }

        private DataTable DtDAT_PROFILE()
        {
            DataTable dt = new DataTable();
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathAccess + ";Jet OLEDB:Database Password=e_spt_21_2009;";
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = connectionString;
            con.Open();

            OleDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "select * from DAT_PROFILE";
            cmd.CommandType = CommandType.Text;

            OleDbDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dt.Load(dr);
            }

            con.Close();

            return dt;
        }

        private string NPWP(DataTable dt)
        {
            string npwp = string.Empty;

            foreach (DataRow dr in dt.Rows)
            {
                npwp = dr[0].ToString();
            }

            return npwp;
        }

        private void ExportForm_Load(object sender, EventArgs e)
        {
            this.ddMasaPajak.SelectedIndex = 11;
            this.cmbYear.SelectedItem = DateTime.Now.Year;
            this.cmbYearTax.SelectedItem = DateTime.Now.AddYears(-1).Year;

            openFileDialog.InitialDirectory = "C:\\";
            openFileDialog.Filter = "txt files (*.dbf)|*.txt|All files (*.*)|*.*";
            openFileDialog.FilterIndex = 2;
            openFileDialog.RestoreDirectory = true;

            openFileDialog1.InitialDirectory = "C:\\Program Files\\DJP\\eSPT PPh 21-26\\Database\\";
            openFileDialog1.Filter = "txt files (*.mdb)|*.txt|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt = DataTableDBF();

            SqlConnection myConnection = new SqlConnection();
            string connString = "Data Source=192.168.50.244;User ID=assdmg;Password=assdmg;Initial Catalog=Astrido-Db;Connection Timeout=10000;";
            myConnection.ConnectionString = connString;

            DataTableReader reader = dt.CreateDataReader();
            myConnection.Open();   ///this is my connection to the sql server

            SqlBulkCopy sqlcpy = new SqlBulkCopy(myConnection);
            sqlcpy.DestinationTableName = "MSALESMA";  //copy the datatable to the sql table

            sqlcpy.WriteToServer(dt);

            myConnection.Close();

            reader.Close();
        }
    }
}